'use strict';
const Controller = require('egg').Controller;
const ExcelJS = require('exceljs');
const moment = require('moment');
const fs = require('fs');
const path = require('path');

class HomeController extends Controller {

  async cities_upload() {
    const { ctx } = this;
    const { savePath } = this.app.config;
    const file = ctx.request.files[0];
    if (!file) throw new Error('没有文件');
    const promise = {};
    promise.promise = new Promise((resolve, reject) => {
      promise.resolve = resolve;
      promise.reject = reject;
    });
    const fsr = fs.createReadStream(file.filepath);
    const outputName = path.basename(file.filepath);
    const fsw = fs.createWriteStream(path.join(savePath, outputName));
    fsw.on('close', () => {
      promise.resolve({
        key: outputName
      });
    });
    fsw.on('error', (e) => {
      console.log('er', e.message);
      promise.reject(new Error('上传失败'));
    });
    fsr.pipe(fsw);
    const data = await promise.promise;
    ctx.success(data);


  }

  async active_info() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const info = await mysql.get('jiaolan_active');
    info.assetsURL = this.app.config.assetsURL;
    ctx.success(info);
  }

  async active_info_edit() {
    const { ctx, app } = this;
    const mysql = ctx.app.mysql;
    const {
      max,
      city_map
    } = ctx.request.body;
    const data = {
      max,
      city_map
    };
    app.assetEmpty(max, '预约上限');
    app.assetEmpty(city_map, '城市底图');

    await mysql.update('jiaolan_active', data, {
      where: {
        active_id: 1
      }
    });


    ctx.success();
  }


  async cities_info() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const { user_phone } = ctx.request.query;
    const paging = ctx.paging();
    const limit = `LIMIT ${paging.limit} offset ${paging.offset} `;
    let where = 'where 1=1 ';
    const params = [];
    if (user_phone) {
      where = `${where} and a.user_phone like ?`;
      params.push(`%${user_phone}%`);
    }

    const list = await mysql.query(`select * from jiaolan_city  ${where} ${limit} `, params);
    const { total } = await mysql.queryOne(`select  count(1) as total from jiaolan_city a ${where}`, params);
    ctx.successPage({
      page: paging.page,
      total,
      data: this.app.changeListTime(list, [ 'start_date', 'end_date' ], 'YYYY-MM-DD'),
    });
  }

  async cities_info_select() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const list = await mysql.select(`jiaolan_city`);
    ctx.success(list.map((item) => {
      return {
        label: item.city_name,
        value: item.city_id
      };
    }));
  }

  async cities_scene_select() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const list = await mysql.select(`jiaolan_scene`);
    ctx.success(list.map((item) => {
      return {
        label: item.scene_name,
        value: item.scene_id
      };
    }));
  }

  async cities_disabled() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const { city_id, date, scene_id } = ctx.request.query;
    const paging = ctx.paging();
    const limit = `LIMIT ${paging.limit} offset ${paging.offset} `;
    let where = 'where 1=1 ';
    const params = [];
    if (city_id) {
      where = `${where} and a.city_id = ?`;
      params.push(city_id);
    }
    if (date) {
      where = `${where} and a.date = ?`;
      params.push(date);
    }
    if (scene_id) {
      where = `${where} and a.scene_id = ?`;
      params.push(scene_id);
    }
    console.log('where', where);

    const list = await mysql.query(`select a.*,b.city_name,c.scene_name from jiaolan_disabled a left join jiaolan_city b on a.city_id  =b.city_id left join jiaolan_scene c on a.scene_id = c.scene_id ${where} order by a.city_id,a.scene_id asc ${limit} `, params);
    const { total } = await mysql.queryOne(`select  count(1) as total from jiaolan_disabled a ${where}`, params);
    ctx.successPage({
      page: paging.page,
      total,
      data: list,
    });
  }

  async cities_disabled_delete() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const {
      id
    } = ctx.request.body;
    await mysql.delete('jiaolan_disabled', {
      id: id
    });

    ctx.success();
  }


  async cities_edit() {
    const { ctx, app } = this;
    const mysql = ctx.app.mysql;
    const {
      city_id,
      city_map,
      city_name,
      sms_address,
      city_sms,
      start_date,
      end_date,
      x,
      y,
      width,
      height
    } = ctx.request.body;
    const data = {
      city_map,
      city_name,
      sms_address,
      city_sms,
      start_date: moment(start_date)
        .toDate(),
      end_date: moment(end_date)
        .toDate(),
      x,
      y,
      width,
      height
    };
    app.assetEmpty(city_name, '城市名称');
    app.assetEmpty(city_sms, '短信名称');
    app.assetEmpty(sms_address, '短信地址');
    app.assetEmpty(start_date, '开始日期');
    app.assetEmpty(end_date, '结束日期');
    app.assetEmpty(city_map, '高亮图片');
    app.assetEmpty(x, '坐标');
    app.assetEmpty(y, '坐标');
    app.assetEmpty(width, '坐标');
    app.assetEmpty(height, '坐标');


    if (city_id) {
      const city = await mysql.get('jiaolan_city', { city_id });
      if (!city) throw new Error('城市不存在');
      await mysql.update('jiaolan_city', data, {
        where: {
          city_id
        }
      });
    } else {
      await mysql.insert('jiaolan_city', data);
    }

    ctx.success();
  }

  async cities_disabled_add() {
    const { ctx, app } = this;
    const mysql = ctx.app.mysql;
    const {
      city_id,
      date,
      scene_id
    } = ctx.request.body;
    const data = {
      city_id,
      date,
      scene_id
    };
    app.assetEmpty(city_id, '禁用城市');
    app.assetEmpty(date, '禁用日期');
    app.assetEmpty(scene_id, '禁用时间');

    const list = await mysql.select('jiaolan_disabled', {
      where: {
        city_id,
        date,
        scene_id
      },
      limit: 1
    });
    if (list.length >= 1) {
      throw new Error('该时间已禁用');
    }


    await mysql.insert('jiaolan_disabled', data);

    ctx.success();
  }

  async cities_delete() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const {
      id
    } = ctx.request.body;
    await mysql.delete('jiaolan_city', {
      city_id: id
    });
    await mysql.delete('jiaolan_disabled', {
      city_id: id
    });

    ctx.success();
  }

  async reserve_info() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const { user_phone, hasWriteOff, channel } = ctx.request.query;
    const paging = ctx.paging();
    const limit = `LIMIT ${paging.limit} offset ${paging.offset} `;
    let where = 'where a.user_phone is not NULL ';
    const params = [];
    if (user_phone) {
      where = `${where} and a.user_phone like ?`;
      params.push(`%${user_phone}%`);
    }
    if (channel) {
      where = `${where} and a.channel like ?`;
      params.push(`%${channel}%`);
    }
    if (hasWriteOff !== undefined) {
      if (Number(hasWriteOff) === 1) {
        where = `${where} and a.hasWriteOff is not null`;
      } else if (Number(hasWriteOff) === 2) {
        where = `${where} and a.hasWriteOff is null`;
      }
    }
    const list = await mysql.query(`select a.openid, a.reserve_time,a.active_id,a.user_name, a.user_phone, a.create_time, a.date, a.hasWriteOff, a.sms_status, a.sms_time,a.channel, b.city_name, c.scene_name from jiaolan_user a left join jiaolan_city b on a.city_id = b.city_id left join jiaolan_scene c on  a.scene_id = c.scene_id  ${where}  order by a.create_time desc ${limit} `, params);
    const { total } = await mysql.queryOne(`select  count(1) as total from jiaolan_user a   ${where}`, params);
    ctx.successPage({
      page: paging.page,
      total,
      data: list
    });
  }

  async reserve_info_export() {
    const { ctx } = this;
    const mysql = ctx.app.mysql;
    const { user_phone, hasWriteOff } = ctx.request.query;
    let where = 'where a.user_phone is not NULL ';
    const params = [];
    if (user_phone) {
      where = `${where} and a.user_phone like ?`;
      params.push(`%${user_phone}%`);
    }
    if (hasWriteOff !== undefined) {
      if (Number(hasWriteOff) === 1) {
        where = `${where} and a.hasWriteOff is not null`;
      } else if (Number(hasWriteOff) === 2) {
        where = `${where} and a.hasWriteOff is null`;
      }
    }
    const list = await mysql.query(`select a.openid,a.reserve_time, a.channel,a.active_id,a.user_name, a.user_phone, a.create_time, a.date, a.hasWriteOff, a.sms_status, a.sms_time, b.city_name, c.scene_name from jiaolan_user a left join jiaolan_city b on a.city_id = b.city_id left join jiaolan_scene c on  a.scene_id = c.scene_id  ${where}  order by a.create_time desc  `, params);
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Sheet1');
    sheet.addRow([ 'openid', '渠道', '用户姓名', '用户手机', '城市名称', '预约日期', '预约时间', '核销时间', '创建时间' ]);
    list.forEach((row) => {
      sheet.addRow([ row.openid, row.channel, row.user_name, row.user_phone, row.city_name, moment(row.date)
        .format('YYYY-MM-DD'), row.scene_name, row.hasWriteOff ? moment(row.hasWriteOff).format('YYYY-MM-DD HH:mm:ss') : '', moment(row.reserve_time)
        .format('YYYY-MM-DD HH:mm:ss') ]);
    });
    ctx.response.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    ctx.response.set('Content-Disposition', `attachment; filename="${encodeURIComponent(`预约信息_${moment()
      .format('YYYYMMDD_hhmmss')}`)}.xls"`);
    const buffer = await workbook.xlsx.writeBuffer();
    ctx.body = buffer;
  }


  async constant_selects() {
    const { ctx } = this;
    ctx.success({
      'orgStatus': [
        {
          'name': '启用',
          'value': 1
        },
        {
          'name': '禁用',
          'value': 2
        }
      ],
      'orgType': [
        {
          'name': '渠道',
          'value': 1
        },
        {
          'name': '终端',
          'value': 2
        }
      ],
      'gender': [
        {
          'name': '女性',
          'value': 0
        },
        {
          'name': '男性',
          'value': 2
        },
        {
          'name': '合影',
          'value': 1
        }
      ],
      'productStatus': [
        {
          'name': '未生效',
          'value': 0
        },
        {
          'name': '已生效',
          'value': 1
        },
        {
          'name': '已结束',
          'value': 2
        },
        {
          'name': '已取消',
          'value': 3
        }
      ],
      'category': [
        {
          'name': '第一类',
          'value': 1
        },
        {
          'name': '第二类',
          'value': 2
        },
        {
          'name': '第三类',
          'value': 3
        },
        {
          'name': '第四类',
          'value': 4
        },
        {
          'name': '第五类',
          'value': 5
        },
        {
          'name': '第六类',
          'value': 6
        },
        {
          'name': '第七类',
          'value': 7
        },
        {
          'name': '第八类',
          'value': 8
        },
        {
          'name': '第九类',
          'value': 9
        },
        {
          'name': '第十类',
          'value': 10
        }
      ]
    });
  }

  async test() {
    const { ctx } = this;
    const { mysql } = this.app;
    await mysql.query('TRUNCATE jiaolan_user');
    const info = '清除成功';
    ctx.success(info);
  }
}

module.exports = HomeController;
